package com.ww.springboot.dd.controller;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.db.sql.SqlExecutor;
import cn.hutool.http.HttpRequest;
import cn.hutool.json.JSONUtil;
import com.alibaba.excel.EasyExcel;
import com.mysql.cj.jdbc.result.ResultSetImpl;
import com.ww.springboot.boot.utils.Func;
import com.ww.springboot.boot.utils.json.JsonUtil;
import com.ww.springboot.dd.amazon.*;
import com.ww.springboot.dd.api.R;
import com.ww.springboot.dd.dto.*;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.codec.Charsets;
import org.apache.commons.io.IOUtils;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.springframework.aop.aspectj.AspectJExpressionPointcut;
import org.springframework.aop.aspectj.AspectJExpressionPointcutAdvisor;
import org.springframework.aop.framework.Advised;
import org.springframework.beans.factory.support.BeanDefinitionBuilder;
import org.springframework.beans.factory.support.DefaultListableBeanFactory;
import org.springframework.beans.factory.support.GenericBeanDefinition;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.StringReader;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.function.Supplier;
import java.util.stream.Collectors;

/**
 * 描述：
 *
 * @author 🧑 ‍wanwei
 * @since 2023-04-21 09:21
 */
@Slf4j
@RestController
@RequestMapping("/sql")
@AllArgsConstructor
public class SqlController {

    @GetMapping("/test")
    public String test() throws InterruptedException {
        Thread.sleep(100000);
        return "";
    }

    @PostMapping("brushSql")
    public String brushSql(String systemCode, @RequestParam("file") MultipartFile multipartFile) throws IOException {
        Map<String, DataSourceProperty> dataSourcePropertiesMap = new TreeMap<>();
        String result = HttpRequest.get("http://opms-service.box-tools.com/client/tenant/db_config_list?appCode=" + systemCode).execute().body();
        TenantDbDTO tenantDbDTO = JSONUtil.toBean(result, TenantDbDTO.class);
        if (!CollectionUtils.isEmpty((Collection) tenantDbDTO.getData())) {
            tenantDbDTO.getData().stream().forEach((tenantDb) -> {
                DataSourceProperty tenantDataSourceProperty = new DataSourceProperty();
                tenantDataSourceProperty.setUrl(tenantDb.getDbUrl()).setUsername(tenantDb.getUsername()).setPassword(tenantDb.getPassword());
                dataSourcePropertiesMap.put(tenantDb.getTenantId(), tenantDataSourceProperty);
            });
        }
        byte[] fileBytes = multipartFile.getBytes();
        File file = File.createTempFile("temp", null);
        FileOutputStream fos = new FileOutputStream(file);
        fos.write(fileBytes);
        fos.close();
        StringBuilder stringBuilder = new StringBuilder();
        for (Map.Entry<String, DataSourceProperty> entry : dataSourcePropertiesMap.entrySet()) {
            try {
                DataSourceProperty property = entry.getValue();
                System.out.println(entry.getKey() + "开始!");
                Connection connection = DriverManager.getConnection(property.getUrl(), property.getUsername(), property.getPassword());
                ScriptRunner scriptRunner = new ScriptRunner(connection);
                scriptRunner.setStopOnError(true);
                scriptRunner.runScript(new StringReader(FileUtil.readString(file, Charset.defaultCharset())));
                connection.close();
                System.out.println(entry.getKey() + "成功!");
            } catch (Exception e) {
                log.error("执行脚本失败!", e);
                if (e.getMessage().contains("command denied to user")) {
                    //无权限 则忽略
                    continue;
                }
                stringBuilder.append(entry.getKey()).append(":").append(e.getMessage()).append(";");
            } finally {
                TenantContext.unbind();
            }
        }
        file.delete();
        return stringBuilder.toString();
    }


    @PostMapping("selectSql")
    public String selectSql(String systemCode, @RequestParam("file") MultipartFile multipartFile) throws IOException {
        Map<String, DataSourceProperty> dataSourcePropertiesMap = new TreeMap<>();
        String result = HttpRequest.get("http://opms-service.box-tools.com/client/tenant/db_config_list?appCode=" + systemCode).execute().body();
        TenantDbDTO tenantDbDTO = JSONUtil.toBean(result, TenantDbDTO.class);
        if (!CollectionUtils.isEmpty((Collection) tenantDbDTO.getData())) {
            tenantDbDTO.getData().stream().forEach((tenantDb) -> {
                DataSourceProperty tenantDataSourceProperty = this.getDataSourceProperty(tenantDb.getTenantId(), dataSourcePropertiesMap);
                tenantDataSourceProperty.setUrl(tenantDb.getDbUrl()).setUsername(tenantDb.getUsername()).setPassword(tenantDb.getPassword());
                dataSourcePropertiesMap.put(tenantDb.getTenantId(), tenantDataSourceProperty);
            });
        }
        byte[] fileBytes = multipartFile.getBytes();
        File file = File.createTempFile("temp", null);
        FileOutputStream fos = new FileOutputStream(file);
        fos.write(fileBytes);
        fos.close();
        StringBuilder stringBuilder = new StringBuilder();
        for (Map.Entry<String, DataSourceProperty> entry : dataSourcePropertiesMap.entrySet()) {
            String key = entry.getKey();
            Integer integer = Integer.valueOf(key);
            if (integer < 16) {
                continue;
            }
            try {
                DataSourceProperty property = entry.getValue();
                System.out.println(entry.getKey() + "开始!");
                Connection connection = DriverManager.getConnection(property.getUrl(), property.getUsername(), property.getPassword());
                String sql = FileUtil.readString(file, Charset.defaultCharset());
                ResultSet resultSet = SqlExecutor.callQuery(connection, sql);
                StringBuilder sb = new StringBuilder();
                boolean flag = false;
                while (resultSet.next()) {
                    flag = true;
                    int length = ((ResultSetImpl) resultSet).getMetadata().getFields().length;
                    for (int i = 1; i <= length; i++) {
                        String value = resultSet.getString(i);
                        sb.append(((ResultSetImpl) resultSet).getMetadata().getFields()[i - 1].getColumnLabel()).append(":").append(value).append(";");
                    }
                }
                if (flag) {
                    stringBuilder.append(entry.getKey()).append(":").append(sb).append("\n");
                }
                connection.close();
                System.out.println(entry.getKey() + "成功!");
            } catch (Exception e) {
                log.error("执行脚本失败!", e);
                if (e.getMessage().contains("command denied to user")) {
                    //无权限 则忽略
                    continue;
                }
                stringBuilder.append(entry.getKey()).append(":").append(e.getMessage()).append(";");
            } finally {
                TenantContext.unbind();
            }
        }
        file.delete();
        return stringBuilder.toString();
    }

    @GetMapping("exportSql")
    public void exportSql(String systemCode, @RequestParam("file") MultipartFile multipartFile, HttpServletResponse response) throws IOException, SQLException {
        Map<String, DataSourceProperty> dataSourcePropertiesMap = new LinkedHashMap<>();
        String result = HttpRequest.get("http://opms-service.box-tools.com/client/tenant/db_config_list?appCode=" + systemCode).execute().body();
        TenantDbDTO tenantDbDTO = JSONUtil.toBean(result, TenantDbDTO.class);
        if (!CollectionUtils.isEmpty((Collection) tenantDbDTO.getData())) {
            tenantDbDTO.getData().stream().forEach((tenantDb) -> {
                DataSourceProperty tenantDataSourceProperty = this.getDataSourceProperty(tenantDb.getTenantId(), dataSourcePropertiesMap);
                tenantDataSourceProperty.setUrl(tenantDb.getDbUrl()).setUsername(tenantDb.getUsername()).setPassword(tenantDb.getPassword());
                dataSourcePropertiesMap.put(tenantDb.getTenantId(), tenantDataSourceProperty);
            });
        }

        byte[] fileBytes = multipartFile.getBytes();
        File file = File.createTempFile("temp", null);
        FileOutputStream fos = new FileOutputStream(file);
        fos.write(fileBytes);
        fos.close();
        String sql = FileUtil.readString(file, Charset.defaultCharset());
        Map<String, String> map = new HashMap<>();
        if (sql.contains("销售id")) {
            //查销售
            map = this.getSaleMap();
        }
        StringBuilder stringBuilder = new StringBuilder();
        List<List<String>> list = new ArrayList<>();
        int saleIndex = -1;
        for (Map.Entry<String, DataSourceProperty> entry : dataSourcePropertiesMap.entrySet()) {
            String key = entry.getKey();
            Integer integer = Integer.valueOf(key);
            if (integer < 16) {
                continue;
            }
            try {
                DataSourceProperty property = entry.getValue();
                System.out.println(entry.getKey() + "开始!");
                Connection connection = DriverManager.getConnection(property.getUrl(), property.getUsername(), property.getPassword());

                ResultSet resultSet = SqlExecutor.callQuery(connection, sql);
                while (resultSet.next()) {
                    int length = ((ResultSetImpl) resultSet).getMetadata().getFields().length;
                    if (ObjectUtil.isEmpty(list)) {
                        List<String> listHead = new ArrayList<>();
                        for (int i = 1; i <= length; i++) {
                            String columnName = ((ResultSetImpl) resultSet).getMetadata().getFields()[i - 1].getColumnLabel();
                            listHead.add(columnName);
                            if (columnName.equals("销售id")) {
                                listHead.add("销售");
                            }
                        }
                        listHead.add("租户");
                        saleIndex = listHead.indexOf("销售id");
                        list.add(listHead);
                    }
                    List<String> listValue = new ArrayList<>();
                    for (int i = 1; i <= length; i++) {
                        listValue.add(resultSet.getString(i));
                        if (saleIndex == i - 1) {
                            listValue.add(map.get(listValue.get(saleIndex)));
                        }
                    }
                    listValue.add(entry.getKey());
                    list.add(listValue);
                }
                connection.close();
                System.out.println(entry.getKey() + "成功!");
                stringBuilder.append(entry.getKey()).append(":").append("成功").append(";");
            } catch (Exception e) {
                log.error("执行脚本失败!", e);
                if (e.getMessage().contains("command denied to user")) {
                    //无权限 则忽略
                    continue;
                }
                stringBuilder.append(entry.getKey()).append(":").append(e.getMessage()).append(";");
            } finally {
                TenantContext.unbind();
            }
        }
        file.delete();
        //EasyExcel.write(new File("D:\\MyFile\\ZJ00014\\zj014\\Desktop\\test\\SQL导出" + System.currentTimeMillis() + ".xlsx")).sheet("").doWrite(list);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding(Charsets.UTF_8.name());
        String fileName = "SQL-EXPORT_" + System.currentTimeMillis();
        String sheetName = "列表";
        fileName = URLEncoder.encode(fileName, Charsets.UTF_8.name());
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream()).sheet(sheetName).doWrite(list);
    }

    private Map<String, String> getSaleMap() throws SQLException {
        Map<String, String> map = new HashMap<>();
        Connection connection = DriverManager.getConnection("jdbc:mysql://10.50.213.155:3306/vest", "vest", "EcwQWUYJOw81MeCr");
        String sql = "select v_id,u_name from vests";
        ResultSet resultSet = SqlExecutor.callQuery(connection, sql);
        while (resultSet.next()) {
            map.put(resultSet.getString(1), resultSet.getString(2));
        }
        return map;
    }

    public DataSourceProperty getDataSourceProperty(String tenantId, Map<String, DataSourceProperty> dataSourcePropertiesMap) {
        DataSourceProperty dsp = null;
        if (dataSourcePropertiesMap.containsKey(tenantId)) {
            dsp = (DataSourceProperty) dataSourcePropertiesMap.get(tenantId);
        } else {
            dsp = new DataSourceProperty();
        }
        return dsp;
    }

    @PostMapping("test1")
    public String test1(String url, String username, String password, String delimiter, @RequestParam("file") MultipartFile multipartFile) throws SQLException, IOException {
        Connection connection = DriverManager.getConnection(url, username, password);
        ScriptRunner scriptRunner = new ScriptRunner(connection);
        //scriptRunner.setSendFullScript(true);
        if (ObjectUtil.isNotEmpty(delimiter)) {
            scriptRunner.setDelimiter(delimiter);
        }
        //scriptRunner.runScript(Resources.getResourceAsReader("xxx.sql"));
        byte[] fileBytes = multipartFile.getBytes();
        File file = File.createTempFile("temp", null);
        FileOutputStream fos = new FileOutputStream(file);
        fos.write(fileBytes);
        fos.close();

        scriptRunner.runScript(new StringReader(FileUtil.readString(file, Charset.defaultCharset())));
        file.delete();
        connection.close();
        return "";
    }


    @GetMapping("exportSql2")
    public String exportSql2() throws IOException, SQLException {
        Map<String, DataSourceProperty> dataSourcePropertiesMap = new LinkedHashMap<>();
        String result = HttpRequest.get("http://opms-service.box-tools.com/client/tenant/db_config_list?appCode=" + "lms-amazon").execute().body();
        TenantDbDTO tenantDbDTO = JSONUtil.toBean(result, TenantDbDTO.class);
        if (!CollectionUtils.isEmpty((Collection) tenantDbDTO.getData())) {
            tenantDbDTO.getData().stream().forEach((tenantDb) -> {
                DataSourceProperty tenantDataSourceProperty = this.getDataSourceProperty(tenantDb.getTenantId(), dataSourcePropertiesMap);
                tenantDataSourceProperty.setUrl(tenantDb.getDbUrl()).setUsername(tenantDb.getUsername()).setPassword(tenantDb.getPassword());
                dataSourcePropertiesMap.put(tenantDb.getTenantId(), tenantDataSourceProperty);
            });
        }

        String sql = "select account,report_id reportId from lms_amazon_report_feed where create_time > \"2024-11-16 00:00:00\" and report_status = 4 group by account limit 1";

        StringBuilder stringBuilder = new StringBuilder();
        List<ReportData> reportDataAllAll = new ArrayList<>();
        for (Map.Entry<String, DataSourceProperty> entry : dataSourcePropertiesMap.entrySet()) {
            String tenantId = entry.getKey();
            Integer integer = Integer.valueOf(tenantId);
            if (integer < 16) {
                continue;
            }
            List<ParamDTO> paramDTOS = this.getData(entry, sql, ParamDTO.class);

            Map<String, List<ParamDTO>> map = paramDTOS.stream().collect(Collectors.groupingBy(ParamDTO::getAccount));

            List<ReportData> reportDataAll = new ArrayList<>();
            for (Map.Entry<String, List<ParamDTO>> entry2 : map.entrySet()) {
                String account = entry2.getKey();
                List<ParamDTO> values = entry2.getValue();
                AuthParamValueVO authParamValueVO = getAuthParamValueVO(tenantId, account,"AM");

                values.forEach(value -> {
                    AmazonAccount amazonAccount = this.convertAuthParamValueVO(authParamValueVO);
                    Map<String, String> param = new HashMap<>(16);
                    param.put("account", JsonUtil.toJson(amazonAccount));
                    param.put("reportId", value.getReportId());
                    R<List<String>> r = HttpUtil.postReport(AmazonApiConstant.getCommonUrl(amazonAccount.getApiUrl(), AmazonApiConstant.SP_GET_REPORT), param, R.class);
                    List<ReportData> reportData = AmRespWrapper.decode(r.getData(), ReportData.class);
                    reportData.stream().forEach(data -> {
                        data.setAccount(account);
                        data.setTenantId(tenantId);
                    });
                    reportDataAll.addAll(reportData);
                });
            }
            reportDataAllAll.addAll(reportDataAll);
        }
        EasyExcel.write(new File("D:\\MyFile\\ZJ00014\\zj014\\Desktop\\test\\SQL导出" + System.currentTimeMillis() + ".xlsx"), ReportData.class)
                .sheet("")
                .doWrite(reportDataAllAll);
        return stringBuilder.toString();
    }

    public static AmazonAccount convertAuthParamValueVO(AuthParamValueVO authParamValueVO) {
        AmazonAccount amazonAccount = new AmazonAccount();
        amazonAccount.setAccountNumber(authParamValueVO.getName());
        amazonAccount.setAccountCode(authParamValueVO.getCode());
        amazonAccount.setAuthToken(authParamValueVO.getToken());
        amazonAccount.setAccessKeyId(authParamValueVO.getParamMap().get("accessKeyId"));
        amazonAccount.setSecretKey(authParamValueVO.getParamMap().get("secretKey"));
        amazonAccount.setMerchantId(authParamValueVO.getParamMap().get("sellerId"));
        MwsMarketplace mwsMarketplace = MwsMarketplace.getMwsMarketplaceBySite(authParamValueVO.getSiteCode());
        if (mwsMarketplace == null) {
            throw new RuntimeException(String.format("根据站点匹配商城编码失败, 请检查,站点:%s", authParamValueVO.getSiteCode()));
        }
        amazonAccount.setSiteCode(mwsMarketplace.getSiteCode());
        amazonAccount.setMarketplaceId(mwsMarketplace.getMarketplaceId());
        amazonAccount.setRefreshToken(authParamValueVO.getParamMap().get("v2RefreshToken"));

        if (Func.isNotBlank(amazonAccount.getRefreshToken())) {
            amazonAccount.setAccessKeyId("AKIA23PQ3TBTA6T5ZQEU");
            amazonAccount.setSecretKey("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");
            amazonAccount.setRoleArn("arn:aws:iam::746218821734:role/app_kedou_role");
            amazonAccount.setClientId("amzn1.application-oa2-client.01220bfa00c9440dae859cff1e2d2c3c");
            amazonAccount.setClientSecret("c1bd17bbcf87638d7c28126c1f658e8ef610d66536f5e0343f9a22552fe93da5");
        }

        if (Func.isBlank(authParamValueVO.getApiUrl())) {
            throw new RuntimeException("ApiUrl, 不能为空");
        }
        amazonAccount.setApiUrl(authParamValueVO.getApiUrl());
        return amazonAccount;
    }

    private static AuthParamValueVO getAuthParamValueVO(String key, String account, String platformCode) {
        String url = "http://sams-service.box-tools.com/client/api-auth/getAuthParamValue";
        String body = HttpRequest.post(url)
                .header("tenantId", key)
                .body("{\n" +
                        "    \"platformCode\": \"" + platformCode + "\",\n" +
                        "    \"names\": [\n" +
                        "        \"" + account + "\"\n" +
                        "    ]\n" +
                        "}")
                .execute()
                .body();


        AuthParamValueVO authParamValueVO = JsonUtil.parse(JsonUtil.toJson(JSONUtil.parseObj(body).getJSONArray("data").get(0)), AuthParamValueVO.class);
        return authParamValueVO;
    }

    private static <T> List<T> getData(Map.Entry<String, DataSourceProperty> entry, String sql, Class<T> clazz) throws SQLException {
        List<T> listData = new ArrayList<>();
        DataSourceProperty property = entry.getValue();
        System.out.println(entry.getKey() + "开始!");
        Connection connection = DriverManager.getConnection(property.getUrl(), property.getUsername(), property.getPassword());

        ResultSet resultSet = SqlExecutor.callQuery(connection, sql);
        while (resultSet.next()) {
            int length = ((ResultSetImpl) resultSet).getMetadata().getFields().length;
            Map<String, String> map = new HashMap<>();
            for (int i = 1; i <= length; i++) {
                map.put(((ResultSetImpl) resultSet).getMetadata().getFields()[i - 1].getColumnLabel(), resultSet.getString(i));
            }
            T t = JsonUtil.parse(JsonUtil.toJson(map), clazz);
            listData.add(t);
        }
        connection.close();
        return listData;
    }
    private static AspectJExpressionPointcutAdvisor getAspectJExpressionPointcutAdvisor(DefaultListableBeanFactory beanFactory) {
        BeanDefinitionBuilder builder = BeanDefinitionBuilder.genericBeanDefinition();
        GenericBeanDefinition beanDefinition = (GenericBeanDefinition) builder.getBeanDefinition();
        beanDefinition.setBeanClass(AspectJExpressionPointcutAdvisor.class);
        AspectJExpressionPointcutAdvisor advisor = new AspectJExpressionPointcutAdvisor();
        advisor.setExpression("");
        //advisor.setAdvice(Objects.requireNonNull(getMethodInterceptor("", "")));
        beanDefinition.setInstanceSupplier((Supplier<AspectJExpressionPointcutAdvisor>) () -> advisor);
        beanFactory.registerBeanDefinition("",beanDefinition);

        return advisor;
    }

    public static void addOrDelAdvice(DefaultListableBeanFactory beanFactory,AspectJExpressionPointcutAdvisor advisor){
        AspectJExpressionPointcut pointcut = (AspectJExpressionPointcut) advisor.getPointcut();
        for (String beanDefinitionName : beanFactory.getBeanDefinitionNames()) {
            Object bean = beanFactory.getBean(beanDefinitionName);
            if(!(bean instanceof Advised)){
                continue;
            }
            Advised advisedBean = (Advised) bean;
            advisedBean.removeAdvice(advisor.getAdvice());
            advisedBean.addAdvice(advisor.getAdvice());
        }
    }

}
